package com.ruoyi.util.excel;

import java.util.Arrays;
import java.util.List;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.stereotype.Service;

/**
 * @Description: 文件处理的公共服务类
 * @ClassName: SysFileService
 * @author: wangwk
 * @date: 2020年12月16日 上午10:35:16
 */
@Service
public class SysImExPortService {


	/**
	 * @Description: 功能描述 设置某些列的值只能输入预制的数据,显示下拉框
	 * 
	 * @param: 参数描述
	 * @param sheet    HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
	 * @param textlist 下拉框内容
	 * @param firstRow 添加下拉框对应开始行
	 * @param endRow   添加下拉框对应结束行
	 * @param firstCol 添加下拉框对应开始列
	 * @param endCol   添加下拉框对应结束列
	 * @param listname 列名称
	 * @return HSSFSheet
	 * @throws: 异常描述
	 *
	 * @version: v1.0.3
	 * @author: syc
	 * @date: 2022年7月29日 上午11:39:16
	 *
	 * Modification History:
	 * Date         Author          Version            Description
	 * ---------------------------------------------------------*
	 * 2022年7月29日   syc          v1.0.3.1               创建
	 */
	private static HSSFSheet setHSSFValidation(HSSFSheet sheet, List<String> mblist, int firstRow, int endRow,
			int firstCol, int endCol, String listname) {
		// 这里将下拉框进行拆分存储在每一个单元格 并进行隐藏 防止下拉框数据过多存储在一个单元格的字符数大于255而报错
		HSSFWorkbook workbook = sheet.getWorkbook();
//		Sheet hidden = workbook.getSheet("hidden");
//		if (hidden == null) {
//			hidden = workbook.createSheet("hidden");
//		} 
		Sheet hidden = workbook.createSheet(listname);
		Cell cell = null;
		for (int i = 0, length = mblist.size(); i < length; i++) {
			String name = mblist.get(i);
			Row roww = hidden.createRow(i);
			cell = roww.createCell(0);
			cell.setCellValue(name);
		}
//		
//		if (namedCell == null ) {
//			
//			namedCell.setNameName("hidden");
//		}
		Name namedCell = workbook.getName(listname);
		namedCell = workbook.createName();
		namedCell.setNameName(listname);

			//namedCell.setRefersToFormula("hidden!$A$1:$A$" + mblist.size());

		 namedCell.setRefersToFormula(listname + "!$A$1:$A$" + mblist.size());
	
		//namedCell.setRefersToFormula("hidden!$" + listname + "$1:$" + listname + "$" + mblist.size());
		workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);
		DVConstraint constraint = DVConstraint.createFormulaListConstraint(listname);
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
     	data_validation_list.createErrorBox("提示", "此值与单元格定义格式不一致");
		sheet.addValidationData(data_validation_list);
		return sheet;
	}
	
	
	/**
     * 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
     *
     * @param sheet  需要添加下拉框的Sheet
     * @param firstRow 起始行
     * @param firstCol 其实列
     * @param endRow   终止行
     * @param endCol   终止列
     * @param dataArray  下拉框数组
     * @param wbCreat    所在excel的WorkBook，用于创建隐藏Sheet
     * @param hidddenSheetName    隐藏Sheet的名称
     * @return
     */
    public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
                                                  int firstCol, int endRow,
                                                  int endCol, String[] dataArray,
                                                  Workbook wbCreat,
                                                  String hidddenSheetName) {

        Sheet hidden = wbCreat.createSheet(hidddenSheetName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = wbCreat.createName();
        namedCell.setNameName(hidddenSheetName);
        namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
        //sheet设置为隐藏
        wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
        //加载数据,将名称为hidden的
        DataValidationConstraint constraint = null;
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
        // 创建 DataValidation
        DataValidation validation = null;
        if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
            validation = dvHelper.createValidation(constraint, addressList);
        } else {
            constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
            validation = new HSSFDataValidation(addressList, constraint);
        }
        if (validation instanceof HSSFDataValidation ) {
            validation .setSuppressDropDownArrow(false);
        } else {
            validation .setSuppressDropDownArrow(true);
            validation .setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
    }

	
	

	/**
	 * @Description: 功能描述
	 *     
	 * 
	 * @param: 参数描述
	 * @param n
	 * @return
	 * @throws: 异常描述
	 *
	 * @version: v1.0.3
	 * @author: syc
	 * @date: 2022年7月29日 上午11:39:16
	 *
	 * Modification History:
	 * Date         Author          Version            Description
	 * ---------------------------------------------------------*
	 * 2022年7月29日   syc          v1.0.3.1               创建
	 */
	public String getListName(int n) {
		if (n >= 16384) {
			//throw new BusinessException("下拉字段大于等于16384,请检查是否正确");
		}
		
		// 总共 16384,从0开始的话就是 16383 (XFD)
		int a = n / 26;
		int b = n % 26;
		List<String> list = Arrays.asList("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O",
				"P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
		if (a > 0 && n < 676) {
			return list.get(a - 1) + list.get(b);
		} else if (n >= 676) {			
			return list.get(a / 26 - 1) + list.get((n-b-(a / 26 * 26 * 26)) / 26 -1) + list.get(b);
		} else {
			return list.get(n);
		}
	}
}
